
var url    = require("url");
var querystring = require("querystring"), fs = require("fs"), Client = require('mysql').Client;
var Client = require('mysql').Client;
var mysql = require('mysql');

/*
 * to handle the detail request of organs on the particular location
 */
exports.getDetails = function(req, res){
	  
	  var client = new Client(); 
	   client.user = 'morgan';
	   client.password = 'm0rgan$$$';
	   client.host="127.0.0.1";
		//client.port="3306";
	   client.database="orgeldb"
	   var list_records = ""
	   var query = "SELECT * from kanton where id>0 AND id<31 AND id!=8 AND id!=9 or id= '' ;";
		
	 	client.query(query, function selectCb(err, results, fields) {
    		if (err) {
      			throw err;
    		}
    		client.end();
    		 list_records = JSON.stringify(results);
	
		});
	
	var location="";
  	var maker;
  	var requestBody;
  	var ortField;
	var echoResponse = url.parse(req.url, true); 
	var geb = echoResponse.query.gebaeude;

	var locations = "SELECT distinct k.name Kanton, ort.name Town,m.id,m.gebaeude Biulding, m.gebaeudeteil 'Biulding Part', m.anzahlmanuale,"+
	"m.tastenumfangmanuale, m.tastenumfangpedal, m.spieltraktur, m.registertraktur, m.spielhilfen, m.stimmtonhoehe, m.balganlage FROM massnahme m "+
    "inner join ort ort on m.ort = ort.id inner join  kanton k " +
    "on ort.kanton = k.id where m.gebaeude = '" +geb +"'"
    
    
    var makers= "SELECT DISTINCT  MIN( d.bezeichnung ) datum ,m.gebaeude, k.name AS name, k.vorname AS vorname "+
	"FROM massnahme AS m INNER JOIN massnahme_kontakt AS mk ON m.id = mk.massnahme "+
	"INNER JOIN kontakt AS k ON mk.kontakt = k.id LEFT OUTER JOIN datum AS d ON m.massnahmeDatum = d.id  where m.gebaeude = '" +geb +"'"+
	"GROUP BY m.gebaeude "
    
    client.query(locations, function selectCb(err, results, fields) {
     	if (!err) {
     		location = JSON.stringify(results);
    	 	client.query(makers, function selectCB(error, resu){
    	 		if(!error){
    	 			maker = JSON.stringify(resu);

					res.render('results.ejs', {
    					results: location,
    					kantons:list_records,
    					res: maker})
    	 		}
    	 		
    	 	})
		} 
	})	
};

/*
 * to check whether an image of the organ exists: if it exists send to the client:
 * is not working: the image is taken now from the legacy site
 */
exports.getImage = function(req, res){
	var client = new Client(); 
	client.user = 'root';
	client.password = '';
	client.host="127.0.0.1";
	//client.port="3306";
	client.database="orgeldb"
	var echoResponse = url.parse(req.url, true); 
	var geb = echoResponse.query.id;
    
	client.query("SELECT daten FROM MEDIUM WHERE id = '" +geb +"' ", function selectCb(err, image, fields) {
    	 if (err) {
      		 throw err;
    	 }
    	 client.end();
    	 if (image.length!=0) {
    	 	res.send(image)
    	 };
	});
			
};
function bin2hex (s) {
    var i, f = 0,
        a = '';
     s += '';
    f = s.length;
 
    for (i = 0; i < f; i++) {
        a += s.charCodeAt(i).toString(16).replace(/^([\da-f])$/, "0$1");    
        }

	return a
}